<?php
class DbHelper
{
	protected function _getExcludeTables() {
		$prefix = Yii::app()->db->tablePrefix;
		$db = $this->_getDbName();
		$sql = "SELECT table_name FROM information_schema.tables WHERE table_schema = '$db' AND table_name NOT LIKE '$prefix%'";
		return Yii::app()->db->createCommand($sql)->queryColumn();
	}
	
	protected function _getDbName() {
		$s = trim(Yii::app()->db->connectionString, ';').';';
		if (preg_match('/dbname=(.*?);/', $s, $matches))
			return($matches[1]);
		return '';
	}
	
	protected function _getDbHost() {
		$s = trim(Yii::app()->db->connectionString, ';').';';
		if (preg_match('/host=(.*?);/', $s, $matches))
			return($matches[1]);
		return '';
	}

	/*
	 * Compress file. Return compressed filename
	 */
	protected function _compress($file, $delFile=true) {
		$output = $file.'.zip';
		$zip = new ZipArchive();
		if ($zip->open($output,ZipArchive::OVERWRITE) !== TRUE) return false;
		$zip->addFile($file,basename($file));
		$zip->close();
		if ($delFile)
			unlink($file);
		return $output;
	}
	
	/*
	 * De-compress file. Return decompressed filename
	 */
	protected function _decompress($file, $delFile=true) {
		$zip = new ZipArchive;
		$res = $zip->open($file);
		if ($res !== TRUE) return false;
		$zip->extractTo(dirname($file));
		$zip->close();
		if ($delFile)
			unlink($file);
		return str_replace('.zip', '', $file);
	}
	
	public function exportAndDownload() {
		// file name to download
		$file = Yii::app()->basePath.'/../download/'.date('Y-m-d-D-').$this->_getDbName().'.sql';
		$dumpSettings = array(
			'exclude-tables' => $this->_getExcludeTables(),
			// 'compress' => 'GZIP',
			'compress' => 'None',
			'no-data' => false,
			'add-drop-database' => false,
			'add-drop-table' => true,
			'single-transaction' => false,
			'lock-tables' => false,
			'add-locks' => false,
			'extended-insert' => true,
			'disable-foreign-keys-check' => false
		);
		$dump = new Mysqldump($this->_getDbName(), Yii::app()->db->username, Yii::app()->db->password, $this->_getDbHost(), 'mysql', $dumpSettings);
		$dump->start($file);
		$file = $this->_compress($file);
		Helper::forceDownload($file);
	}
	
	public function import($file) {
		if (!is_file($file)) return false;
		
		// uncompress file
		$ext = pathinfo($file, PATHINFO_EXTENSION);
		$zipTypes = array('zip');
		if (in_array($ext, $zipTypes)) {
			$file = $this->_decompress($file);
		}
		
		// parse file content to sql commands
		$cmds = SqlParser::parse(file_get_contents($file));
		if (!is_array($cmds)) return false;
		foreach($cmds as $cmd) {
			Yii::app()->db->createCommand($cmd)->execute();
		}
		return true;
	}
}